#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
insert into hive.zx_dws.dws_apply_theme
with temp as (
    select substring(create_date_time, 1, 10) as create_date,
           itcast_school_id                   as school_id,
           school_name,
           case
               when origin_type = 'NETSERVICE' then '线上'
               when origin_type = 'SCHOOL' then '线下'
               when origin_type = 'OTHER' then '线下'
               when origin_type = 'VISITED' then '线下'
               when origin_type = 'PHONE' then '线下'
               else null end                  as origin_type,
           itcast_subject_id                  as subject_id,
           subject_name,
           origin_channel,
           department_id,
           department_name,
           payment_state,
           deleted,       -- 是否被删除/禁用 判断意向人数
           appeal_status, -- 申诉状态 0:待稽核 1:无效 2：有效

           -- 去重
           row_number() over (partition by substring(create_date_time, 1, 10), origin_type, itcast_school_id, itcast_subject_id,id) as rn_1,
           row_number() over (partition by substring(create_date_time, 1, 10), origin_type,origin_channel,id)                       as rn_2,
           row_number() over (partition by substring(create_date_time, 1, 10),origin_type, department_id,id)                        as rn_3,
           dt
    from hive.zx_dwb.dwb_apply_detail
	where dt = '${TD_DATE}'
)
select create_date,                                                      -- 时间
       school_id,
       school_name,
       origin_type,                                                      -- 线上线下
       subject_id,
       subject_name,
       origin_channel,
       department_id,
       department_name,
       count(if(payment_state = 'PAID', 1, null)) as apply_count,        -- 报名人数
       count(if(deleted = 0, 1, null))            as intention_count,    -- 意向人数
       count(if(appeal_status is null or appeal_status = 2, 1, null))      as valid_thread_count, -- 有效线索人数

       -- 分组标记 group_mark
       case
           when grouping(create_date, origin_type, school_id, subject_id) = 0 then 4
           when grouping(create_date, origin_type, subject_id) = 0 then 3
           when grouping(create_date, origin_type, school_id) = 0 then 2
           when grouping(create_date, school_id) = 0 then 1
           when grouping(create_date, origin_type, origin_channel) = 0 then 5
           when grouping(create_date, origin_type, department_id) = 0 then 6
           when grouping(create_date, origin_type) = 0 then 7
           else null end                          as group_mark,
       -- 分区字段
       max(dt) as dt
from temp
where rn_1 = 1
  and rn_2 = 1
  and rn_3 = 1
group by
    grouping sets ( (create_date, school_id, school_name),                                        -- 每日各个校区
                    (create_date, origin_type, school_id, school_name),                           -- 每日线上线下各个校区
                    (create_date, origin_type, subject_id, subject_name),                         -- 每日线上线下各个学科
                    (create_date, origin_type, school_id, school_name, subject_id, subject_name), -- 每日线上线下各个校区各个学科
                    (create_date, origin_type, origin_channel),                                   -- 每日线上线下各个来源渠道(来源渠道字段是空字符串)
                    (create_date, origin_type, department_id, department_name),                   -- 每日线上线下各个咨询中心
                    (create_date, origin_type)                                                    -- 每日线上线下
    );"